﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

/***********************************************************
* Description: SQL CLR
* Date:   $DATE$ $TIME$
* Author: 刘俊
*
*   Modified By: 刘俊
* Modified Date: 2013-07-12
*      Comments: 将条件中";"替换为","。主要应用于RTX中。
************************************************************
*  __                        _____                     
* /\ \       __             /\___ \                    
* \ \ \     /\_\  __  __    \/__/\ \  __  __    ___    
*  \ \ \  __\/\ \/\ \/\ \      _\ \ \/\ \/\ \ /' _ `\  
*   \ \ \LJ \\ \ \ \ \_\ \    /\ \_\ \ \ \_\ \/\ \/\ \ 
*    \ \____/ \ \_\ \____/    \ \____/\ \____/\ \_\ \_\
*     \/___/   \/_/\/___/      \/___/  \/___/  \/_/\/_/
************************************************************/

public partial class UserDefinedFunctions
{
    #region  重载GetMulitiValue(),增加其它查询条件参数
    //使用方法：SELECT [dbo].[GetMulitiValue]('hrmresource','lastname','id','911,229,1410,1395,1017,662,717,23100','And (1=1)')
    /// <summary>
    /// 将获取的单列多个行值转为以逗号分隔的字符串
    /// </summary>
    /// <param name="table">表</param>
    /// <param name="returnField">返回的字段</param>
    /// <param name="whereField">查询的字段</param>
    /// <param name="whereStr">条件</param>
    /// <param name="othercondition">其它查询条件</param>
    /// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,IsDeterministic = true)]
    public static SqlString GetMulitiValue2(string table,string returnField,string whereField,string whereStr,string othercondition)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            if (string.IsNullOrEmpty(whereStr)||whereStr.Trim().Length<1)
            {
                return null;
            }
            else
            {
                whereStr = whereStr.Replace(';', ',');
                string tempStr = null;
                string[] wh = whereStr.Split(',');

                for (int i = 0; i < wh.Length; i++)
                {
                    switch (i < 1)
                    {
                        case true: tempStr += string.Format("'{0}'", wh[i]);
                            break;
                        case false:
                            tempStr += string.Format(",'{0}'", wh[i]);
                            break;
                    }
                }
                
                string v = "";
                SqlCommand cmd = new SqlCommand("SELECT " + returnField + " FROM " + table + " WHERE " + whereField + " in (" + tempStr + ") "+othercondition, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();

                DataSet ds = new DataSet();
                adapter.Fill(ds);
                if (ds.Tables[0].Rows.Count <1 )
                {
                    return null;
                }
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    v +=","+ds.Tables[0].Rows[i][0].ToString();
                }
                return v.Substring(1);
            }

           

        }
    }
    #endregion

};

